﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //FillDataToGridView();
        FillData();
    }

    private void FillDataToGridView() {
        SqlConnection cnn = new SqlConnection("Data Source=LONGDT\\SQLEXPRESS;Initial Catalog=Northwind;User ID=sa;Password=123456");
        
        DataSet dsCustomers = new DataSet();
        SqlDataAdapter adt = new SqlDataAdapter("Select * from Customers",cnn);
        adt.Fill(dsCustomers);
        GridView1.DataSource = dsCustomers;
        GridView1.DataBind();
    }
    
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        //dsCustomersTableAdapters.CustomersTableAdapter adt = new dsCustomersTableAdapters.CustomersTableAdapter();
        //dsCustomers.CustomersDataTable tb = new dsCustomers.CustomersDataTable();

        string id = txtSearch.Text;

        //adt.FillBy(tb, id);
        //GridView1.DataSource = tb;
        //GridView1.DataBind();


        /*Cah 1: Fill lai du lieu*/
        SearchDataOldType(id);
    }

    //Fill Data to GridView
    void FillData() {
        // Su dung .Net Data Provider: SqlConnection de xu ly du lieu voi co so du lieu MSSQL
        // B1: Xu ly Connected:
            // Tao ket noi:
        SqlConnection cnn = new SqlConnection("Data Source=LONGDT\\SQLEXPRESS;Initial Catalog=Northwind;User ID=sa;Password=123456");
            //Su dung SqlCommand su dung cau query truy xuat du lieu tu DB 
        string query = "select * from Customers";
        SqlCommand command = new SqlCommand(query,cnn);
            // Khoi tao doi tuong DataAdapter: De Fill du lieu sang DataSet
        SqlDataAdapter adapter = new SqlDataAdapter();
            // Doi duong adapter nay can fai co thong tin chinh xac ve: nguon du lieu, duong truyen va kieu xu ly ==> duoc nam trong command
        adapter.SelectCommand = command;
        //B2: Xu ly DisConnected:
            // Tao DataSet nhu la noi chua du lieu
        DataSet ds = new DataSet();
            //Dung adapter de Fill du lieu vao ds vua tao
        adapter.Fill(ds);
            // Dong ket noi.
        cnn.Close();        
        //B3: Hien thi. Nhu vay la trong Doi tuong DataSet ds hien gio da chua thong tin la bang Customer tu CSDL: Northwind, 
        // cau truc bang dc lay tu SqlCommand command voi cau query nhu tren. De hien thi ra o GridView, thi GridView la mot doi tuong dc 
        // phat trien de co the hieu duoc kieu du lieu nay thong qua thuoc tinh DataSource.
        GridView1.DataSource = ds;
        /* De hien thi len thi ta dung phuong thuc DataBind(). Dieu nay hoi khac WinForm, vi ben Winform chi can den buoc datasource kia
        la okie. Vay tai sao lai fai can DataBind()?! Anh chua tim hieu, nhung anh co fan doan nay: Do la do khi ASP.NET bien dich code
        sang ma HTML, thi kieu du lieu DataSet loi tu DB ra, no phai xu ly ntn de co the bien dich sang kieu ma HTML theo cau truc table.
         => Can mot fuong thuc de giai quyet van de tren. Do la : DataBind()...Va con nhieu ly luan nua anh ko the ngoi viet van o day dc.        
        Va day: hien thi ra */
        GridView1.DataBind();
        /* Day la nhung cau lenh demo de chung ta co the hinh dung dc cach ADO.NET xu ly tung viec 1.
         Khi code that, thi can de y viec Open-Close connect */
    }

    //Search Data
        /*
            C1: Thay doi cau query, Fill lai du lieu.
         */
    void SearchDataOldType(string text) {
        SqlConnection cnn = new SqlConnection("Data Source=LONGDT\\SQLEXPRESS;Initial Catalog=Northwind;User ID=sa;Password=123456");
        DataSet dsCustomers = new DataSet();
        SqlDataAdapter adt = new SqlDataAdapter("select * from Customers where CustomerID like'"+text+"%'", cnn);
        adt.Fill(dsCustomers);
        GridView1.DataSource = dsCustomers;
        GridView1.DataBind();    
    }
        /*
            C2: Chi tim kiem du lieu tai bang du lieu da dc lay ra va hien thi tai GridView
         */
    void SearchDataInMyDataSet(string text) {
        
        
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        MyDataTableAdapters.EmployeesTableAdapter employeesAdapter = new MyDataTableAdapters.EmployeesTableAdapter();
        MyData.EmployeesDataTable employeeTable = new MyData.EmployeesDataTable();
        employeesAdapter.Fill(employeeTable);

        MyDataTableAdapters.OrdersTableAdapter orderAdapter = new MyDataTableAdapters.OrdersTableAdapter();
        MyData.OrdersDataTable orderTable = new MyData.OrdersDataTable();
        orderAdapter.Fill(orderTable);

        GridView1.DataSource = employeeTable;
        GridView1.DataBind();

        GridView2.DataSource = orderTable;
        GridView2.DataBind();
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        dsCustomersTableAdapters.CustomersTableAdapter adt = new dsCustomersTableAdapters.CustomersTableAdapter();
        dsCustomers.CustomersDataTable tb = new dsCustomers.CustomersDataTable();             
        string id = txtSearch.Text;
        adt.SearchById(tb,id);

        GridView1.DataSource = tb;
        GridView1.DataBind();
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        FillData();
    }
}